//Originally written by Michael Toscano and Geoff McElhanon
//I modified it to fit the providers for SmartCodeGenerator.
//This modified version is written by Shahed Khan, shahed.khan@gmail.com

//==============================================================
//Copyright (C) 2004, Michael Toscano and Geoff McElhanon.  All Rights Reserved.
//
//This library is free software; you can redistribute it and/or
//modify it under the terms of the GNU Lesser General Public
//License as published by the Free Software Foundation; either
//version 2.1 of the License, or (at your option) any later version.
//
//This library is distributed in the hope that it will be useful,
//but WITHOUT ANY WARRANTY; without even the implied warranty of
//MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
//Lesser General Public License for more details.
//
//	NOTE: This copyright notice must accompany all modified versions of this code.
//



//public enum ExtensionType
//{
//	CS_Default,
//	CS_IdentitySeed,
//	CS_IdentityIncrement,
//	CS_IsComputed,
//	CS_IsDeterministic,
//	CS_IsIdentity,
//	CS_IsRowGuidCol
//}

using System;
using System.Collections;
using System.Collections.Specialized;
using Oracle.DataAccess.Client;
using System.Data;
using System.Text;
using System.Text.RegularExpressions;
using Utility.Configuration;
using SmartCodeGen.Providers;

namespace SmartCodeGen.ImplementedProviders
{
    public class OracleDataAccessDBSchemaProvider : DBSchemaProvider
	{
		private bool mySchemaOnly = true;
		private bool autoCreateExtendedPropertiesTable = false;
		private string extendedPropertiesTableOwner = null;
		private string extendedPropertiesTableOwnerAsPrefix = null;
		private bool allowGetCommandResultSchemas = false;

        public OracleDataAccessDBSchemaProvider()
		{
            //if (CustomConfigurationSettings.AppSettings["AllowGetCommandResultSchemas"] != null)
            //{
            //    try 
            //    {
            //        allowGetCommandResultSchemas = (bool) Convert.ChangeType(CustomConfigurationSettings.AppSettings["AllowGetCommandResultSchemas"], typeof(bool));
            //    }
            //    catch {}
            //}

            //if (CustomConfigurationSettings.AppSettings["ShowMySchemaOnly"] != null)
            //{
            //    try 
            //    {
            //        mySchemaOnly = (bool) Convert.ChangeType(CustomConfigurationSettings.AppSettings["ShowMySchemaOnly"], typeof(bool));
            //    }
            //    catch {}
            //}

            //if (CustomConfigurationSettings.AppSettings["AutoCreateExtendedPropertiesTable"] != null)
            //{
            //    try 
            //    {
            //        autoCreateExtendedPropertiesTable = (bool) Convert.ChangeType(CustomConfigurationSettings.AppSettings["AutoCreateExtendedPropertiesTable"], typeof(bool));
            //    }
            //    catch {}
            //}

            //extendedPropertiesTableOwner = CustomConfigurationSettings.AppSettings["ExtendedPropertiesTableOwner"];

            //// If an explicit owner name was provided, use it, and also build up the variable to be used for prefixing
            //if (extendedPropertiesTableOwner != null && extendedPropertiesTableOwner.Length > 0)
            //{
            //    extendedPropertiesTableOwnerAsPrefix = extendedPropertiesTableOwner + ".";
            //}
            //else
            //{
            //    // We don't want an empty string... we want it to always be 'null'
            //    extendedPropertiesTableOwner = null;
            //}
		}

		#region IDbSchemaProvider Members

        #region DbSchemaProvider Members

        /// <summary>
		/// Return the Constant NAME of this SchemaProvider
		/// </summary>
		public override string GetName()
		{
            return "OracleDataAccessSchemaProvider (using Oracle.DataAccess.Client namespace)";
		}
		/// <summary>
		/// Describe yourself
		/// </summary>
        public override string GetDescription( )
        {
            return "Oracle schema provider using Oracle.DataAccess.Client namespace. Modified by Shahed Khan shahed.khan@gmail.com to fit SmartCodeGenerator, Implementation by Michael Toscano (mailto:VikingCoder@VikingSoftware.com) and Geoff McElhanon (mailto:gmcelhanon@austin.rr.com).";
        }		
		#endregion
	
		#region Database Info
		/// <summary>
		/// Returns the DB name 
		/// </summary>
		/// <param name="DatabaseSchema"></param>
		/// <returns></returns>
        public override string GetDatabaseName(DatabaseSchema database)
		{
			string databaseName = null;
			string userName = null;

			// Open a connection
			using (OracleConnection conn = new OracleConnection(database.ConnectionString))
			{
				conn.Open();
				databaseName = conn.DataSource;

				// Parse the connect string
                Hashtable connectStringParms = ParseConnectStringToHashTable(database.ConnectionString);
				
				// Look for the user name in acceptable forms
				if (connectStringParms["user id"] != null)
					userName = (string) connectStringParms["user id"];
				else if (connectStringParms["uid"] != null)
					userName = (string) connectStringParms["uid"];
				else if (connectStringParms["user"] != null)
					userName = (string) connectStringParms["user"];

				// If username was found, append it to the database name in parenthesis
				if (userName != null && userName.Length > 0)
				{
					return string.Format("{0} ({1})", databaseName, userName);
				}
				else 
				{
					return databaseName;
				}
			}
		}

		private Hashtable ParseConnectStringToHashTable(string connectString) 
		{
			string[] nameValuePairs = connectString.Split(';');

			Hashtable ht = new Hashtable(nameValuePairs.Length);

			foreach (string s in nameValuePairs) 
			{
				if( s.Length > 0 ) 
				{
					string[] nameAndValue = s.Split('=');
					if( nameAndValue.Length >= 2 ) 
					{
						ht.Add(nameAndValue[0].ToLower(), nameAndValue[1]);
					}
				}
			}

			return ht;
		}


		public override ExtendedPropertyCollection GetExtendedProperties(SchemaBase schemaObject)
		{
			// Get the schema object name and type name to be used to retrieve the extended property.
            string connectionString = schemaObject.ConnectionString;
			string containerObjectOwner = null;
			string objectName = schemaObject.Name;
			string schemaObjectTypeName = schemaObject.GetType().Name;

			switch (schemaObjectTypeName)
			{
				case "ColumnSchema":
					containerObjectOwner = ((ColumnSchema) schemaObject).Table.Owner;

					ColumnSchema column = (ColumnSchema) schemaObject;
					objectName = column.Table.Name + "." + column.Name;
					break;

				case "ViewColumnSchema":
					containerObjectOwner = ((ViewColumnSchema) schemaObject).View.Owner;

					ViewColumnSchema viewColumn = (ViewColumnSchema) schemaObject;
					objectName = viewColumn.View.Name + "." + viewColumn.Name;
					break;

				case "CommandSchema":
					containerObjectOwner = ((CommandSchema) schemaObject).Owner;
					break;

				case "DatabaseSchema":
					objectName = "Database"; // TODO - Use user name here instead?

					containerObjectOwner = "Database";
					break;

				case "IndexSchema":
					containerObjectOwner = ((IndexSchema) schemaObject).Table.Owner;  // SchemaExplorer ignores index ownership, use the table's owner instead.
					break;

				case "ParameterSchema":
					containerObjectOwner = ((ParameterSchema) schemaObject).Command.Owner;
					break;

				case "PrimaryKeySchema":
					containerObjectOwner = ((PrimaryKeySchema) schemaObject).Table.Owner; // SchemaExplorer ignores constraint ownership, use the table's owner instead.
					break;

				case "TableKeySchema":
					containerObjectOwner = ((TableKeySchema) schemaObject).ForeignKeyTable.Owner; // SchemaExplorer ignores constraint ownership, use the foreign key table owner instead.
					break;

				case "TableSchema":
					containerObjectOwner = ((TableSchema) schemaObject).Owner;
					break;

				case "ViewSchema":
					containerObjectOwner = ((ViewSchema) schemaObject).Owner;
					break;

				default:
					// Don't recognize the SchemaExplorer type... need to throw an exception
					throw new NotSupportedException(string.Format("Unexpected SchemaExplorer type '{0}' encountered.  Extended properties are not yet supported for this type.", schemaObjectTypeName));
			}

			// Define SQL to pull appropriate extended properties from the table
			string sql = string.Format(@"
					SELECT	property_name, property_value, clr_type
					FROM	{0}codesmith_extended_properties
					WHERE	container_object_owner = '{1}'
							AND object_name = '{2}'
							AND codesmith_schema_type = '{3}'",
				extendedPropertiesTableOwnerAsPrefix,
				containerObjectOwner.ToUpper(),
				objectName.ToUpper(),
				schemaObjectTypeName);

			const int POS_PROPERTY_NAME = 0;
			const int POS_PROPERTY_VALUE = 1;
			const int POS_CLR_TYPE = 2;

			// Instantiate an ArrayList to hold the extended properties temporarily
			//ArrayList propertyList = new ArrayList();
            ExtendedPropertyCollection propertyList = new ExtendedPropertyCollection();

			using (OracleConnection conn = new OracleConnection(connectionString))
			{
				conn.Open();

				try 
				{
					using (OracleCommand cmd = new OracleCommand(sql, conn))
					{
						using (OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
						{
							while (reader.Read()) 
							{
								// Pull all the values into variables
								string propertyName = reader.GetString(POS_PROPERTY_NAME);
								string propertyValueText = reader.GetString(POS_PROPERTY_VALUE);
								string clrTypeName = reader.GetString(POS_CLR_TYPE);

								// Get the System type for the value
								Type clrType = Type.GetType(clrTypeName);

								// Convert the string representation of the property to the appropriate CLR type
								object propertyValue = Convert.ChangeType(propertyValueText, clrType);

								// Construct and add the extended property 
								propertyList.Add(new ExtendedProperty(propertyName, propertyValue, GetDbTypeFromSystemType(clrType))); 
							}
						}
					}
				} 
				catch (Exception ex)
				{
					// Check for the Oracle exception that indicates that the table isn't there
					if (ex.Message.IndexOf("ORA-00942") >= 0)
					{
						// Should we try to automatically create the table?
						if (autoCreateExtendedPropertiesTable)
						{
							// Try to create the extended properties table
							CreateExtendedPropertiesTable(connectionString);

							// If we're still here, close the connection so recursive call doesn't try to open up another one needlessly
							conn.Close();

							// If we're still here, it was successful, so try to get property again
							return GetExtendedProperties(schemaObject);
						}
						else
						{
							throw new ApplicationException(string.Format("Extended properties table '{0}CODESMITH_EXTENDED_PROPERTIES' does not yet exist.", extendedPropertiesTableOwnerAsPrefix), ex);
						}
					}
					else
					{
						throw ex;
					}
				}

				// Return the extended properties as an array
				//return (ExtendedProperty[]) propertyList.ToArray(typeof(ExtendedProperty));
                return propertyList;
			}
		}

		/// <summary>
		/// Converts the provided system type into the corresponding DbType .
		/// </summary>
		/// <param name="type">The System type to be converted.</param>
		/// <returns>The corresponding DbType.</returns>
		private DbType GetDbTypeFromSystemType(System.Type type)
		{	
			if (type == typeof(System.String))
			{
				return DbType.String; // DbType.StringFixedLength, DbType.AnsiString, DbType.AnsiStringFixedLength
			}
			else if (type == typeof(System.Boolean))
			{
				return DbType.Boolean;
			}
			else if (type == typeof(System.Byte))
			{
				return DbType.Byte;
			}
			else if (type == typeof(System.SByte))
			{
				return DbType.SByte;
			}
			else if (type == typeof(System.Int16))
			{
				return DbType.Int16;
			}
			else if (type == typeof(System.UInt16))
			{
				return DbType.UInt16;
			}
			else if (type == typeof(System.Int32))
			{
				return DbType.Int32;
			}
			else if (type == typeof(System.UInt32))
			{
				return DbType.UInt32;
			}
			else if (type == typeof(System.Int64))
			{
				return DbType.Int64;
			}
			else if (type == typeof(System.UInt64))
			{
				return DbType.UInt64;
			}
			else if (type == typeof(System.Single))
			{
				return DbType.Single;
			}
			else if (type == typeof(System.Double))
			{
				return DbType.Double;
			}
			else if (type == typeof(System.Decimal))
			{
				return DbType.Decimal;
			}
			else if (type == typeof(System.DateTime))
			{
				return DbType.DateTime; // DbType.Date, DbType.Time
			}
			else if (type == typeof(System.Byte[]))
			{
				return DbType.Binary;
			}
			else
			{
				throw new NotSupportedException(string.Format("Conversion of System type '{0}' to a corresponding DbType is unhandled.", type.ToString()));

				// Unhandled conversions:
				//		DbType.Currency:
				//		DbType.Guid:
				//		DbType.Object:
				//		DbType.VarNumeric:
			}
		}

		#endregion

		#region TABLE Info

		/// <summary>
		/// Get the Array of tables...
		/// </summary>
		/// <param name="connectionString"></param>
		/// <param name="database"></param>
		/// <returns></returns>
        public override TableSchemaCollection GetTables(DatabaseSchema database)
        {
            return GetTables(database, database.Name);
        }
        public override TableSchemaCollection GetTables(DatabaseSchema database, string databaseName)
        {
            string connectionString = database.ConnectionString;
			//ArrayList tableList = new ArrayList();
            TableSchemaCollection tableList = new TableSchemaCollection();

			using (OracleConnection conn = new OracleConnection(connectionString))
			{
				// Open a connection
				conn.Open();

				const int POS_OWNER = 0;
				const int POS_NAME = 1;
				const int POS_CREATED = 2;

				string sql = string.Format(@"
						SELECT		owner, object_name, created 
						FROM		all_objects
						WHERE		{0}
									AND object_type = 'TABLE'      
						ORDER BY	owner, 
									object_name", 
					GetSchemaOwnershipFilter());

				using (OracleCommand cmd = new OracleCommand(sql, conn))
				{
					using (OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
					{
						while (reader.Read())
						{
							// Exclude the extended properties table if it is encountered
							if (reader.GetString(POS_NAME).ToUpper() != "CODESMITH_EXTENDED_PROPERTIES")
							{
								TableSchema tableSchema = new TableSchema( database, reader.GetString( POS_NAME ), reader.GetString( POS_OWNER ), reader.GetDateTime( POS_CREATED ) );
								tableList.Add( tableSchema );
							}
						}
					}
				}
			}

			//return (TableSchema[]) tableList.ToArray( typeof( TableSchema ));
            return tableList;
		}


		/// <summary>
		/// Get all the Indexes for a given Table
		/// </summary>
		/// <param name="connectionString"></param>
		/// <param name="table"></param>
		/// <returns></returns>
		/// 
		public override IndexSchemaCollection GetTableIndexes(TableSchema table)
		{
            string connectionString = table.ConnectionString;
			string sql = string.Format(@"
				select	    idx.owner, idx.uniqueness, con.constraint_type, idx.table_type, col.*
				from		all_ind_columns col,
							all_indexes idx,
							all_constraints con
				where		idx.table_owner = '{0}'
                            AND idx.table_name = '{1}'
                            AND idx.owner = col.index_owner
                            AND idx.index_name = col.index_name
                            AND idx.owner = con.owner (+)
							AND idx.table_name = con.table_name(+)
							AND idx.index_name = con.constraint_name(+)", table.Owner, table.Name);

			// Collections to hold index information
			NameValueCollection indexMemberCollection = new NameValueCollection();
			Hashtable isUniqueByIndexName = new Hashtable();
			Hashtable isPrimaryByIndexName = new Hashtable();
			Hashtable isClusteredByIndexName = new Hashtable();

			using (OracleConnection conn = new OracleConnection(connectionString))
			{
				// Open a connection
				conn.Open();

				using (OracleCommand cmd = new OracleCommand(sql, conn))
				{
					using (OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
					{
						int constraintTypeOrdinal = reader.GetOrdinal("CONSTRAINT_TYPE");
						int tableTypeOrdinal = reader.GetOrdinal("TABLE_TYPE");

						while (reader.Read())
						{
							// Add the column to the collection by index name
							indexMemberCollection.Add((string) reader["INDEX_NAME"], (string) reader["COLUMN_NAME"]);
				
							// Determine if index is unique
							bool isUnique = ((string) reader["UNIQUENESS"] == "UNIQUE");
							isUniqueByIndexName[(string) reader["INDEX_NAME"]] = isUnique;

							// Determine if index is the primary key index
							bool isPrimary = (!reader.IsDBNull(constraintTypeOrdinal) && (string) reader["CONSTRAINT_TYPE"] == "P");
							isPrimaryByIndexName[(string) reader["INDEX_NAME"]] = isPrimary;

							// Determine if the index is on a TABLE or CLUSTER
							// NOTE: A Microsoft?SQL Server?clustered index is not like an Oracle cluster. 
							// An Oracle cluster is a physical grouping of two or more tables that share the 
							// same data blocks and use common columns as a cluster key. SQL Server does not 
							// have a structure that is similar to an Oracle cluster.
							bool isClustered = ((string) reader["TABLE_TYPE"] == "CLUSTER");
							isClusteredByIndexName[(string) reader["INDEX_NAME"]] = isClustered;
						}
					}
				}
			}

			// Declare an array to hold the index schemas to be returned
			//IndexSchema[] indexSchemas = new IndexSchema[indexMemberCollection.Count];
            IndexSchemaCollection indexSchemas = new IndexSchemaCollection();

			// Were there any index members found?
			if (indexMemberCollection.Count > 0)
			{
				// Iterate through the distinct keys (index names) of the NameValueCollection
				int i = 0;
				foreach (string indexName in indexMemberCollection.AllKeys)
				{
					IndexSchema indexSchema = new IndexSchema(
						table, 
						indexName, 
						(bool) isPrimaryByIndexName[indexName],
						(bool) isUniqueByIndexName[indexName], 
						(bool) isClusteredByIndexName[indexName], 
						indexMemberCollection.GetValues(indexName));

					indexSchemas[i++] = indexSchema;
				}
			}

			// Return the array of indexes
			return indexSchemas;
		}

		#region Michael's Original GetTableIndexes Implementation (commented out)
//		public IndexSchema[] GetTableIndexes(string connectionString, TableSchema table)
//		{
//			ArrayList IndexList = new ArrayList();
//
//			// Open a connection
//			OracleConnection conn = new OracleConnection(connectionString);
//			conn.Open();
//
//			const int POS_NAME = 0;
//			const int POS_TYPE = 1;
//			const int POS_UNIQ = 2;
//
//			using (conn)
//			{
//				string sql = string.Format( 
//					@"select index_name, table_type, uniqueness
//					    from all_indexes idx
//					   where table_owner='{0}'
//					 	 and table_name = '{1}'
//				    order by index_name",
//					table.Owner ,
//					table.Name  );
//
//
//				OracleCommand cmd = new OracleCommand(sql, conn);
//				using (cmd)
//				{
//					OracleDataReader reader = cmd.ExecuteReader();
//
//					while (reader.Read())
//					{
//						IndexSchema idx = GetIndexSchema( conn, table, reader.GetString( POS_NAME ),
//							reader.GetString( POS_TYPE ),
//							reader.GetString( POS_UNIQ ) );
//
//						IndexList.Add( idx );
//					}
//
//				}
//				conn.Close();
//			}
//
//			// Return the array of commands
//			return (IndexSchema[]) IndexList.ToArray(typeof(IndexSchema));
//		}
		#endregion

		/// <summary>
		/// Return the Columns for a given Table.
		/// </summary>
		/// <param name="connectionString"></param>
		/// <param name="table"></param>
		/// <returns></returns>
		public override ColumnSchemaCollection GetTableColumns(TableSchema table)
		{
			//ArrayList columnList = new ArrayList();
            ColumnSchemaCollection columnList = new ColumnSchemaCollection();

			using (OracleConnection conn = new OracleConnection(table.ConnectionString))
			{
				// Open a connection
				conn.Open();

				const int POS_COLUMN_NAME	= 0;
				const int POS_DATA_TYPE		= 1;
				//const int POS_data_length	= 2;
				const int POS_DATA_PRECISION= 3;
				const int POS_DATA_SCALE	= 4;
				const int POS_NULLABLE		= 5;
//				const int POS_COMMENTS		= 6;

				string sql = string.Format( 
					@"select cols.column_name, 
							 cols.data_type, 
							 cols.data_length,
							 cols.data_precision, 
							 cols.data_scale,
							 cols.nullable,        
							 cmts.comments
					  from  all_tab_columns cols, 
							all_col_comments cmts 
					  where 
							cols.owner = '{0}'
						and cols.table_name = '{1}'
						and cols.owner = cmts.owner 
						and cols.table_name = cmts.table_name 
						and cols.column_name = cmts.column_name
						order by column_id",
					table.Owner ,
					table.Name  );

				using (OracleCommand cmd = new OracleCommand(sql, conn))
				{
					using (OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
					{
						while (reader.Read())
						{
							//ExtendedProperty[] properties = new ExtendedProperty[1];
                            ExtendedPropertyCollection properties = new ExtendedPropertyCollection();
							properties[0] = CreateExtendedPropertyForComments(reader);

							ColumnSchema cs = new ColumnSchema( 
								table, 
								reader.GetString(POS_COLUMN_NAME), 
								GetDbType(reader),
								reader.GetString(POS_DATA_TYPE), 
								GetDBTypeLength(reader),
								reader.IsDBNull(POS_DATA_PRECISION) ? (byte)0 : Convert.ToByte( reader[POS_DATA_PRECISION]),
								reader.IsDBNull(POS_DATA_SCALE    ) ?       0 : Convert.ToInt32( reader[POS_DATA_SCALE]   ),
								reader.GetString( POS_NULLABLE ) == "Y",
								properties);

							columnList.Add( cs );
						}
					}
				}
			}

			// Return the array of columns
			//return (ColumnSchema[]) columnList.ToArray(typeof(ColumnSchema));
            return columnList;
		}

		/// <summary>
		/// Return the Foreign key info for a given table...
		/// </summary>
		/// <param name="connectionString"></param>
		/// <param name="table"></param>
		/// <returns></returns>
		public override TableKeySchemaCollection GetTableKeys( TableSchema table)
		{
            string connectionString = table.ConnectionString;
			//ArrayList tableSchemaList = new ArrayList();
            TableKeySchemaCollection tableSchemaList = new TableKeySchemaCollection();

			using (OracleConnection conn = new OracleConnection(connectionString))
			{
				// Open a connection
				conn.Open();

				string sql = string.Format( 
					@"select 
				    cols.constraint_name, 
				    cols.column_name, 
				    cols.position, 
				    r_cons.table_name related_table_name, 
				    r_cols.column_name related_column_name 
				from
				    all_constraints     cons,
				    all_cons_columns    cols,
				    all_constraints     r_cons,
				    all_cons_columns    r_cols
				where cons.OWNER = '{0}'
				  and cons.table_name = '{1}'
				  and cons.constraint_type='R'
				  and cols.owner = cons.owner
				  and cols.table_name = cons.table_name   
				  and cols.constraint_name = cons.constraint_name 
				  and r_cols.owner = cons.r_owner 
				  and r_cols.constraint_name = cons.r_constraint_name 
				  and r_cons.owner = r_cols.owner 
				  and r_cons.table_name = r_cols.table_name 
				  and r_cons.constraint_name = r_cols.constraint_name 
				order by cons.constraint_name, cols.position",
					table.Owner,
					table.Name);

				// GKM - Should we change to OracleDataReader implementation?
				OracleDataAdapter da = new OracleDataAdapter( sql, conn ); 

				DataSet ds = new DataSet();
				using (ds)
				{
					da.Fill( ds );

					string lastConstraint = string.Empty;
					foreach (DataRow row in ds.Tables[0].Rows )
					{
						if (lastConstraint!=row["constraint_name"].ToString())
						{
							lastConstraint=row["constraint_name"].ToString();

							string pkTable = row["related_table_name"].ToString();

							DataRow [] distinctColumns = ds.Tables[0].Select(
								string.Format("constraint_name = '{0}'", lastConstraint) ); 

							ArrayList fkMemberCols = new ArrayList();
							ArrayList pkMemberCols = new ArrayList();

							foreach( DataRow rdc in distinctColumns)
							{
								fkMemberCols.Add( rdc["column_name"].ToString());
								pkMemberCols.Add( rdc["related_column_name"].ToString());
							}

							TableKeySchema tks = new TableKeySchema( 
								table.Database,
								lastConstraint,
								(string[])fkMemberCols.ToArray(typeof(string)),
								table.Name,
								(string[])pkMemberCols.ToArray(typeof(string)),
								pkTable );
							
							// Don't forget to add it to our result
							tableSchemaList.Add( tks );
						}
					}

				}
			}
			
			//return (TableKeySchema[]) tableSchemaList.ToArray(typeof(TableKeySchema));
            return tableSchemaList;
		}

		
		/// <summary>
		/// Return the PK for a given Table...
		/// </summary>
		/// <param name="connectionString"></param>
		/// <param name="table"></param>
		/// <returns></returns>
		public override PrimaryKeySchema GetTablePrimaryKey(TableSchema table)
		{
			PrimaryKeySchema primaryKeySchema = null;

			using (OracleConnection conn = new OracleConnection(table.ConnectionString))
			{
				// Open a connection
				conn.Open();

				string sql = string.Format(@"
					select 
						cols.constraint_name, 
						cols.column_name, 
						cols.position 
					from
						all_constraints     cons,
						all_cons_columns    cols
					where 
						cons.OWNER = '{0}'
						and cons.table_name = '{1}'
						and cons.constraint_type='P'
						and cols.owner = cons.owner
						and cols.table_name = cons.table_name   
						and cols.constraint_name = cons.constraint_name 
					order by cons.constraint_name, cols.position",
						table.Owner,
						table.Name);

				// GKM - Should we change to OracleDataReader implementation?
				OracleDataAdapter da = new OracleDataAdapter( sql, conn ); 

				using (DataSet ds = new DataSet())
				{
					int rowCount = da.Fill( ds );

					if (rowCount > 0)
					{
						ArrayList memberCols = new ArrayList();

						foreach (DataRow row in ds.Tables[0].Rows )
						{
							memberCols.Add( row["column_name"].ToString());
						}

						primaryKeySchema = new PrimaryKeySchema( table, 
													 ds.Tables[0].Rows[0]["constraint_name"].ToString(),
													 (string[])memberCols.ToArray( typeof(string)));
					}
				}
			}
			
			return primaryKeySchema;
		}


		/// <summary>
		/// Return the data from a table in a System.DataTable object...
		/// </summary>
		/// <param name="connectionString"></param>
		/// <param name="table"></param>
		/// <returns></returns>
		public override DataTable GetTableData(TableSchema table)
		{
			using (OracleConnection conn = new OracleConnection(table.ConnectionString))
			{
				// Create and fill the data table
				DataTable dt = new DataTable(table.Name);
			
				string sql = string.Format( "SELECT * FROM {0}.{1}" , table.Owner, table.Name);

				using (OracleDataAdapter adp = new OracleDataAdapter(sql, conn))
				{
					adp.Fill(dt);
				}

				return dt;
			}
		}
		#endregion

		#region VIEW Info

		/// <summary>
		/// Return array of Views
		/// </summary>
		/// <param name="connectionString"></param>
		/// <param name="database"></param>
		/// <returns></returns>
		public override ViewSchemaCollection GetViews(DatabaseSchema database)
		{
            string connectionString = database.ConnectionString;
			// Build SQL to get the views
			const int POS_OWNER_NAME = 0;
			const int POS_VIEW_NAME = 1;
			const int POS_CREATED = 2;

			string sql = string.Format(
 			  @"select 
					v.owner, v.view_name, o.created
				from all_views   v,
					all_objects o 
				where v.view_name = o.object_name 
				and o.object_type = 'VIEW' 
				and {0}
				order by v.owner, v.view_name",
				GetSchemaOwnershipFilter("v"));

			// Create an array list to hold the values temporarily
			//ArrayList viewList = new ArrayList();
            ViewSchemaCollection viewList = new ViewSchemaCollection();

			using (OracleConnection conn = new OracleConnection(connectionString))
			{
				// Open a connection
				conn.Open();

				using (OracleCommand cmd = new OracleCommand( sql, conn))
				{
					using (OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
					{
						while (reader.Read())
						{
							viewList.Add(new ViewSchema( database, reader.GetString(POS_VIEW_NAME), reader.GetString( POS_OWNER_NAME ), reader.GetDateTime(POS_CREATED)));
						}
					}
				}
			}

			//return (ViewSchema[]) viewList.ToArray(typeof(ViewSchema));
            return viewList;
		}


		/// <summary>
		/// Returns the Text of a View's definition
		/// </summary>
		/// <param name="connectionString"></param>
		/// <param name="view"></param>
		/// <returns></returns>
		public override string GetViewText(ViewSchema view)
		{
			string viewText = null;

			using (OracleConnection conn = new OracleConnection(view.ConnectionString))
			{
				// Open a connection
				conn.Open();

				// Get SQL to retrieve source for the view
				string sql = string.Format(@"
				select		text
				from		all_views
				where		owner = '{0}'
							and view_name = '{1}'", view.Owner, view.Name);

				// Get the view text
				using (OracleCommand cmd = new OracleCommand(sql, conn))
				{
					viewText = (string) cmd.ExecuteScalar();
				}
			}

			return viewText;
		}

		/// <summary>
		/// Return all the columns for a view...
		/// </summary>
		/// <param name="connectionString"></param>
		/// <param name="view"></param>
		/// <returns></returns>
		public override ViewColumnSchemaCollection GetViewColumns(ViewSchema view)
		{
            string connectionString = view.ConnectionString;
			//ArrayList columnList = new ArrayList();
            ViewColumnSchemaCollection columnList = new ViewColumnSchemaCollection();

			using (OracleConnection conn = new OracleConnection(connectionString))
			{
				// Open a connection
				conn.Open();

				const int POS_COLUMN_NAME	= 0;
				const int POS_DATA_TYPE		= 1;
				//const int POS_data_length	= 2;
				const int POS_DATA_PRECISION= 3;
				const int POS_DATA_SCALE	= 4;
				//const int POS_nullable		= 5;
				// const int POS_COMMENTS		= 6;

				string sql = string.Format( 
					@"select cols.column_name, 
							 cols.data_type, 
							 cols.data_length,
							 cols.data_precision, 
							 cols.data_scale,
							 cols.nullable,        
							 cmts.comments
					  from  all_tab_columns cols, 
							all_col_comments cmts 
					  where 
							cols.owner = '{0}'
						and cols.table_name = '{1}'
						and cols.owner = cmts.owner 
						and cols.table_name = cmts.table_name 
						and cols.column_name = cmts.column_name
						order by column_id",
					view.Owner ,
					view.Name  );


				using (OracleCommand cmd = new OracleCommand(sql, conn))
				{
					using (OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
					{
						while (reader.Read())
						{
							//ExtendedProperty[] properties = new ExtendedProperty[1];
                            ExtendedPropertyCollection properties = new ExtendedPropertyCollection();
							properties[0] = CreateExtendedPropertyForComments(reader);

							ViewColumnSchema vcs = new ViewColumnSchema( 
								view , 
								reader.GetString(POS_COLUMN_NAME),
								GetDbType(reader),
								reader.GetString(POS_DATA_TYPE), 
								GetDBTypeLength(reader),
								reader.IsDBNull(POS_DATA_PRECISION) ? (byte)0 : Convert.ToByte( reader[POS_DATA_PRECISION]),
								reader.IsDBNull(POS_DATA_SCALE    ) ?       0 : Convert.ToInt32( reader[POS_DATA_SCALE]   ),
								properties);

							columnList.Add( vcs );
						}					
					}
				}
			}

			// Return the array of columns
			//return (ViewColumnSchema[]) columnList.ToArray(typeof(ViewColumnSchema));
            return columnList;
		}


		/// <summary>
		/// Return all the Rows from a view
		/// </summary>
		/// <param name="connectionString"></param>
		/// <param name="view"></param>
		/// <returns></returns>
		public override DataTable GetViewData(ViewSchema view)
		{
			// Open a connection
			using (OracleConnection conn = new OracleConnection(view.ConnectionString))
			{
				// Create and fill the data table
				DataTable dt = new DataTable(view.Name);
			
				string sql = string.Format( "SELECT * FROM {0}.{1}" , view.Owner, view.Name);

				using (OracleDataAdapter adp = new OracleDataAdapter(sql, conn))
				{
					adp.Fill(dt);
				}

				return dt;
			}
		}

		#endregion

		#region COMMAND Info

		/// <summary>
		/// Return array of commands...
		/// </summary>
		/// <param name="connectionString"></param>
		/// <param name="database"></param>
		/// <returns></returns>
		public override CommandSchemaCollection GetCommands(DatabaseSchema database)
		{
            string connectionString = database.ConnectionString;
			//ArrayList commandList = new ArrayList();
            CommandSchemaCollection commandList = new CommandSchemaCollection();

			using (OracleConnection conn = new OracleConnection(connectionString))
			{
				// Open a connection
				conn.Open();

				const int POS_OWNER		= 0;
				const int POS_PACKAGE	= 1;
				const int POS_METHOD	= 2;
				const int POS_OVERLOAD	= 3;
				const int POS_TYPE		= 4;
				const int POS_CREATED	= 5;
				const int POS_STATUS	= 6;
				const int POS_OBJ_ID	= 7;

				string sql = string.Format(
					@"	select methods.owner, 
							methods.package_name, 
							methods.object_name, 
							methods.overload,
							ao.object_type,
							ao.created,
							ao.status,
							ao.object_id
						from
						(select distinct owner, package_name, object_name, overload, object_id from ALL_ARGUMENTS 
							where {0}
							) methods,
							all_objects ao
						where ao.object_id = methods.object_id    
						order by methods.owner, methods.package_name, methods.object_name",
						GetSchemaOwnershipFilter(null));

				using (OracleCommand cmd = new OracleCommand(sql, conn))
				{
					using (OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
					{
						while (reader.Read())
						{
							ExtendedPropertyCollection extendedProperties = new ExtendedPropertyCollection();
							extendedProperties[0]= new ExtendedProperty( "CS_ObjectType", reader.GetString( POS_TYPE ), DbType.String);
							extendedProperties[1]= new ExtendedProperty( "CS_Status", reader.GetString( POS_STATUS ), DbType.String);
							extendedProperties[2]= new ExtendedProperty( "CS_ObjectId", reader.GetInt64(POS_OBJ_ID), DbType.Int64 );
						
							Int32 overload = -1;
							if (reader[POS_OVERLOAD].ToString().Length > 0)
							{
								overload = Int32.Parse(reader[POS_OVERLOAD].ToString());
							}
							extendedProperties[3]= new ExtendedProperty( "CS_OverloadNumber", overload, DbType.Int32 );

							string name = null;

							if ("PACKAGE"==reader.GetString( POS_TYPE))
							{
								name = string.Format( "{0}.{1}",reader.GetString(POS_PACKAGE) , reader.GetString( POS_METHOD ));
							}
							else
							{
								name = reader.GetString( POS_METHOD );
							}

							// Save the name and full name (minus the "hack" to support overloads) to the extended properties
							extendedProperties[4] = new ExtendedProperty("CS_Name", name, DbType.String);
							extendedProperties[5] = new ExtendedProperty("CS_FullName", reader.GetString(POS_OWNER) + "." + name, DbType.String);
							
							// Tack on overload # into the name as a means of supporting overloads in CodeSmith.
							// Template writers will need to use the CS_Name property to get just the command name value,
							// but this at least allows code to be generated that makes use of this great Oracle feature.
							if (overload >= 0) // GKM - Actually > 0 would work because first overload is given ID of 1, second is 2, and so on.
							{
								name += "[" + overload.ToString() + "]";
							}

							CommandSchema commandSchema = new CommandSchema( database, 
								name, 
								reader.GetString( POS_OWNER ), 
								reader.GetDateTime( POS_CREATED ), 
								extendedProperties );

							commandList.Add(commandSchema);
						}
					}
				}
			}

			// Return the array of commands
			//return (CommandSchema[]) commandList.ToArray(typeof(CommandSchema));
            return commandList;
		}

		public override ParameterSchemaCollection GetCommandParameters(CommandSchema command)
		{
            string connectionString = command.ConnectionString;
			//ArrayList paramList = new ArrayList();
            ParameterSchemaCollection paramList = new ParameterSchemaCollection();

			Int64  object_id  = (Int64)command.ExtendedProperties["CS_ObjectId"].Value;
			Int32  overload   = (Int32)command.ExtendedProperties["CS_OverloadNumber"].Value;
			
			string packageName = string.Empty;
			string methodName = string.Empty;
			string commandName = (string) command.ExtendedProperties["CS_Name"].Value;
			
			int posDot = commandName.IndexOf('.');
			if (posDot>0)
			{
				packageName = commandName.Substring(0, posDot);
				methodName = commandName.Substring( posDot+1 );
			}
			else
			{
				methodName = commandName;
			}

			using (OracleConnection conn = new OracleConnection(command.ConnectionString))
			{
				// Open a connection
				conn.Open();

				const int POS_ARGUMENT_NAME = 0;
				const int POS_DATA_TYPE		= 4;
				const int POS_IN_OUT		= 5;
				const int POS_DATA_PRECISION= 7;
				const int POS_DATA_SCALE	= 8;

				string sql = string.Format(
					@"select 
						ARGUMENT_NAME, 
						POSITION, 
						SEQUENCE, 
						DATA_LEVEL, 
						DATA_TYPE, 
						IN_OUT, 
						DATA_LENGTH, 
						DATA_PRECISION, 
						DATA_SCALE  
					from ALL_ARGUMENTS 
					where object_ID={0}
					and object_name = '{1}'
					and {2}
					order by position",
					object_id,
					methodName,
					overload > 0 ? "overload = " + overload : "overload is null" );

				using (OracleCommand cmd = new OracleCommand(sql, conn))
				{
					using (OracleDataReader reader = cmd.ExecuteReader())
					{
						while (reader.Read())
						{
							if (reader.IsDBNull( POS_DATA_TYPE ))
							{
								continue;
							}

							byte precision = 0;
							if (!reader.IsDBNull(POS_DATA_PRECISION))
							{
								precision = Convert.ToByte( reader[POS_DATA_PRECISION]);
							}

							Int32 scale = 0;
							if (!reader.IsDBNull(POS_DATA_SCALE))
							{
								scale = Convert.ToInt32( reader[POS_DATA_SCALE]   );
							}

							string argName = null;
							if (reader.IsDBNull( POS_ARGUMENT_NAME))
							{
								argName = "RETURN";
							}
							else					  
							{
								argName = reader.GetString( POS_ARGUMENT_NAME );
							}

							ParameterSchema parameterSchema = new ParameterSchema( 
								command, 
								argName,
								GetParameterDirection( reader.IsDBNull( POS_ARGUMENT_NAME ), reader.GetString(  POS_IN_OUT )),
								GetDbType( reader ),
								reader.GetString( POS_DATA_TYPE ),
								GetDBTypeLength( reader ),
								precision,
								scale,
								true );

							paramList.Add( parameterSchema );
						}
					}
				}
			}

			//return (ParameterSchema[]) paramList.ToArray(typeof(ParameterSchema));
            return paramList;
		}

		public override string GetCommandText(CommandSchema command)
		{
			// TODO: Return command text of simple procedures?
			throw new NotImplementedException("Retrieval of command text has not yet been implemented.");
		}

		public override CommandResultSchemaCollection GetCommandResultSchemas(CommandSchema command)
		{
			// Initialize array to size 0 for now
			CommandResultSchemaCollection commandResultSchemas = new CommandResultSchemaCollection();

			// Make sure the user has explicitly allowed this operation to proceed...
			if (allowGetCommandResultSchemas == false)
			{
				throw new InvalidOperationException("The Oracle schema provider supports reading column schema information, but this option must be turned on in the DLL's configuration file as its usage can change the state of the server.");
			}

			// Keep a count as to how many results are expected
			int refCursorCount = 0;
			
			using (OracleConnection conn = new OracleConnection(command.ConnectionString))
			{
				// Open the connection
				conn.Open();
				
				using (OracleCommand cmd = new OracleCommand((string) command.ExtendedProperties["CS_FullName"].Value, conn))
				{
					cmd.CommandType = CommandType.StoredProcedure;

					ArrayList resultSchemaNames = new ArrayList();

					// Set up the parameters will null values
					foreach (ParameterSchema parameterSchema in command.Parameters)
					{
						OracleParameter parm = new OracleParameter();
						
						parm.ParameterName = parameterSchema.Name;
						parm.DbType = parameterSchema.DataType;
						parm.Direction = parameterSchema.Direction;
						
						// If the parameter is not a REF CURSOR
						if (parm.DbType != DbType.Object || parameterSchema.NativeType != "REF CURSOR")
						{
							// Assign a null value
							parm.Value = DBNull.Value;

							#region Commented out code trying to solve a particular problem during testing
//							if (parameterSchema.AllowDBNull)
//							{
//								// Assign a null value
//								parm.Value = DBNull.Value;
//							}
//							else
//							{
//								switch (parm.DbType)
//								{
//									case DbType.AnsiString:
//									case DbType.AnsiStringFixedLength:
//									case DbType.String:
//									case DbType.StringFixedLength:
//										parm.Size = parameterSchema.Size;
//										parm.Value = string.Empty;
//										break;
//
//									case DbType.Boolean:
//									case DbType.Byte:
//									case DbType.Currency:
//									case DbType.Decimal:
//									case DbType.Double:
//									case DbType.Int16:
//									case DbType.Int32:
//									case DbType.Int64:
//									case DbType.SByte:
//									case DbType.Single:
//									case DbType.UInt16:
//									case DbType.UInt32:
//									case DbType.UInt64:
//									case DbType.VarNumeric:
//										parm.Value = 1;
//										break;
//
//									case DbType.Date:
//									case DbType.DateTime:
//									case DbType.Time:
//										parm.Value = new DateTime(1967,1,1);
//										break;
//
//									case DbType.Guid:
//										throw new NotSupportedException("Encountered unsupported DbType.Guid.");
//
//									default:
//										throw new NotSupportedException(string.Format("Encountered unsupported type 'DbType.{0}'", parm.DbType.ToString()));
//								}
//							}
//						}
							#endregion
						}
						else
						{
							// Keep track of the ref cursor variable names to use for naming the resultset
							resultSchemaNames.Add(parameterSchema.Name);

							// Set the ref cursor type
							//parm.OracleType = OracleType.Cursor;

							// Make note as to how many refcursor parms we have
							refCursorCount++;
						}

						// Add the parameter to the command for execution
						cmd.Parameters.Add(parm);
					}

					// Will there be any results returned?
					if (refCursorCount > 0)
					{
						// Wrap the operation in a transaction to prevent as much change to the server as possible
						using (OracleTransaction trans = conn.BeginTransaction())
						{
							// Assign the transaction to the command
							cmd.Transaction = trans;

							// Ask for reader, but the schema only.
							using (OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.CloseConnection))
							{
								int colIndex = 0;
								int refCursorIndex = 0;

								// Create an array to hold information about all the resultsets
								//commandResultSchemas = new CommandResultSchema[refCursorCount];
                                commandResultSchemas = new CommandResultSchemaCollection();

								do
								{
									// Get schema info from reader
									DataTable schemaTable = reader.GetSchemaTable();

									// Create an array of result column schemas to store column information
									//CommandResultColumnSchema[] resultColumnSchemas = new CommandResultColumnSchema[schemaTable.Rows.Count];
                                    CommandResultColumnSchemaCollection resultColumnSchemas = new CommandResultColumnSchemaCollection();

									// Iterate through all rows in the schema table
									foreach (DataRow row in schemaTable.Rows)
									{
										string nativeType;

										// TODO: This needs work because we don't get Base Table/Column information.  Native type information
										// would have to be inferred from the DbType, which wouldn't be accurate.
										try
										{
											TableSchema tableSchema = command.Database.Tables[(string) row["BaseSchemaName"], (string) row["BaseTableName"]];
											nativeType = tableSchema.Columns[(string) row["BaseColumnName"]].NativeType;
										}
										catch 
										{
											nativeType = "--UNKNOWN--";
										}

										CommandResultColumnSchema resultColumnSchema = 
											new CommandResultColumnSchema(
												command, 
												(string) row["ColumnName"],
												GetDbTypeFromSystemType((System.Type) row["DataType"]),
												nativeType,
												Convert.ToInt32(row["ColumnSize"]),
												Convert.ToByte(row["NumericPrecision"]),
												Convert.ToInt32(row["NumericScale"]),
												(bool) row["AllowDBNull"]);
									
										// Assign the result column schema information to the array
										resultColumnSchemas[colIndex++] = resultColumnSchema;
									}

									// Construct a result schema for the results just processed
									CommandResultSchema commandResultSchema = new CommandResultSchema(command, (string) resultSchemaNames[refCursorIndex], resultColumnSchemas);

									// Assign the result schema for this resultset to the array
									commandResultSchemas[refCursorIndex++] = commandResultSchema;
								} while (reader.NextResult());
							}
						}
					}
				}

				// Return the array of command results
				return commandResultSchemas;
			}

			#region Michael's implementation (commented out)
//			ArrayList resultList = new ArrayList();
//
//			ParameterSchema[] cmdParams = GetCommandParameters( connectionString, command );
//
//			foreach( ParameterSchema ps in cmdParams )
//			{
//				if (ps.Direction == System.Data.ParameterDirection.ReturnValue)
//				{
//					CommandResultColumnSchema[] crcsArray = new CommandResultColumnSchema[1];
//					crcsArray[0] = new CommandResultColumnSchema( command, ps.Name, ps.DataType, ps.NativeType, ps.Size, ps.Precision, ps.Scale, ps.AllowDBNull );
//
//					CommandResultSchema crs = new CommandResultSchema(
//						command,
//						ps.Name,
//						crcsArray);
//
//					resultList.Add( crs );
//				}
//			}
//
//			return (CommandResultSchema[]) resultList.ToArray(typeof(CommandResultSchema));
			#endregion
		}

		#endregion

		#endregion

		private void CreateExtendedPropertiesTable(string connectionString)
		{
			string sqlCreate = string.Format(@"
				CREATE TABLE {0}codesmith_extended_properties
				(
					container_object_owner     VARCHAR2(50) NOT NULL,
					object_name                VARCHAR2(61) NOT NULL,
					codesmith_schema_type      VARCHAR2(200) NOT NULL,
					property_name              VARCHAR2(75) NOT NULL,
					property_value             VARCHAR2(4000),
					clr_type                   VARCHAR2(50) NOT NULL
				)", extendedPropertiesTableOwnerAsPrefix);

			string sqlAddPK = string.Format(@"
				ALTER TABLE {0}codesmith_extended_properties
					ADD CONSTRAINT	pkcodesmithextendedproperties PRIMARY KEY
					(
						container_object_owner,
						object_name,
						codesmith_schema_type,
						property_name
					)", extendedPropertiesTableOwnerAsPrefix);

			using (OracleConnection conn = new OracleConnection(connectionString))
			{
				conn.Open();

				try
				{
					using (OracleCommand cmd = new OracleCommand(sqlCreate, conn))
					{
						cmd.ExecuteNonQuery();
					}
				}
				catch (Exception ex)
				{
					// Wrap the exception in our own, and rethrow
					throw new ApplicationException("Unable to automatically create extended properties because the following exception occurred:\n\n" + ex.Message, ex);
				}

				try
				{
					using (OracleCommand cmd = new OracleCommand(sqlAddPK, conn))
					{
						cmd.ExecuteNonQuery();
					}
				}
				catch (Exception ex)
				{
					// Wrap the exception in our own, and rethrow
					throw new ApplicationException("Unable to add primary to extended properties table during auto-creation because the following exception occurred:\n\n" + ex.Message, ex);
				}
			}
		}

		#region Helpers

		private string GetSchemaOwnershipFilter()
		{
			return GetSchemaOwnershipFilter(null);
		}

		/// <summary>
		/// Filter what we see to be only our objects.
		/// </summary>
		/// 
		private string GetSchemaOwnershipFilter(string aliasOfTableContainingOwnerColumn)
		{
			if (aliasOfTableContainingOwnerColumn == null)
			{
				aliasOfTableContainingOwnerColumn = string.Empty;
			}
			else if (aliasOfTableContainingOwnerColumn.Length > 0)
			{
				aliasOfTableContainingOwnerColumn += ".";
			}

			if (mySchemaOnly)
			{
				return string.Format( "({0}owner in ( select USERNAME from user_users  ))", aliasOfTableContainingOwnerColumn);
			}
			else
			{
				return string.Format( "(not {0}owner in ('SYS', 'SYSTEM'))", aliasOfTableContainingOwnerColumn);
			}
		}

		#region Extended Property Help
		private ExtendedProperty CreateExtendedPropertyForComments(OracleDataReader reader)
		{
			int ordinal = reader.GetOrdinal("COMMENTS");

			if (ordinal >= 0)
			{
				// Get the comments, or an empty string
				string comments = reader.IsDBNull(ordinal) ? string.Empty : reader.GetString(ordinal);

				return CreateExtendedPropertyForComments(comments);
			}
			else
			{
				throw new ApplicationException("There was no COMMENTS column in the OracleDataReader provided.");
			}
		}

		private ExtendedProperty CreateExtendedPropertyForComments(string comments)
		{
			ExtendedProperty property = new ExtendedProperty("CS_Description", comments, DbType.String);

			return property;
		}
		#endregion

		#region GetIndexSchema (commented out)
//		/// <summary>
//		/// Get the Index Schema for a given Index...
//		/// </summary>
//		/// <param name="conn"></param>
//		/// <param name="table"></param>
//		/// <param name="indexName"></param>
//		/// <param name="tableType"></param>
//		/// <param name="uniqueness"></param>
//		/// <returns></returns>
//		private IndexSchema GetIndexSchema(OracleConnection conn, TableSchema table, string indexName, string tableType, string uniqueness)
//		{
//			IndexSchema rVal = null;
//
//			ArrayList memberColumns = new ArrayList();
//
//			const int POS_COLNAME = 1;
//
//			string sql = string.Format( 
//				@"select
//							col.column_position,
//							col.column_name,  
//							idx.UNIQUENESS, 
//							col.DESCEND  
//					from	 
//							all_indexes idx,
//							all_ind_columns col
//					where		
//							idx.table_owner = '{0}'
//					and  idx.table_name = '{1}'
//					and  idx.index_name='{2}'
//					and  col.table_owner = idx.table_owner 
//					and  col.index_name  = idx.index_name 
//				order by  
//							col.column_position",
//				table.Owner ,
//				table.Name, 
//				indexName );
//
//			OracleCommand cmd = new OracleCommand(sql, conn);
//			using (cmd)
//			{
//				OracleDataReader reader = cmd.ExecuteReader();
//
//				while (reader.Read())
//				{
//					memberColumns.Add( reader.GetString( POS_COLNAME ));
//				}
//			}
//
//			rVal = new IndexSchema( table, indexName, GetConstraintType(conn, table.Owner, table.Name , indexName)=='P', uniqueness=="UNIQUE", tableType != "TABLE", (string[])memberColumns.ToArray( typeof(string) ));
//			
//			
//			return rVal;
//		}
		#endregion

		#region DbType Conversions
		#region Commented out conversions
//		private System.Data.DbType GetDbType(string nativeType)
//		{
//			return GetDbType(nativeType, 0, 0);
//		}

//		private OracleType GetOracleType(DbType type)
//		{
//			switch (type)
//			{
//				case DbType.AnsiString:
//				case DbType.AnsiStringFixedLength:
//				case DbType.Binary:
//				case DbType.Boolean:
//				case DbType.Byte:
//				case DbType.Currency:
//				case DbType.Date:
//				case DbType.DateTime:
//				case DbType.Decimal:
//				case DbType.Double:
//				case DbType.Guid:
//				case DbType.Int16:
//				case DbType.Int32:
//				case DbType.Int64:
//				case DbType.Object:
//				case DbType.SByte:
//				case DbType.Single:
//				case DbType.String:
//				case DbType.StringFixedLength:
//				case DbType.Time:
//				case DbType.UInt16:
//				case DbType.UInt32:
//				case DbType.UInt64:
//				case DbType.VarNumeric:
//				default:
//					throw new NotSupportedException(string.Format("Unexpected DbType '{0}' is not supported by Oracle schema provider.", type.ToString()));
//			}
//		}
		#endregion

		private System.Data.DbType GetDbType(string nativeType, int precision, int scale)
		{
			switch (nativeType.ToUpper()) 
			{
				case "FLOAT":
				case "DEC":
				case "DECIMAL":
				case "NUMERIC":
					return DbType.Decimal;

				case "INTEGER":
				case "INT":
					return DbType.Int32; // Really?

				case "SMALLINT":
					return DbType.Int16; // Really?

				case "PLS_INTEGER":
				case "BINARY_INTEGER":
					return DbType.Int32;

				case "NUMBER":
					if ( scale != 0 && scale != 255 ) 
					{
						// Real number
						if (precision > 28) 
						{
							return DbType.VarNumeric;  // Should be DbType.Decimal?
						}
						else if (precision > 15) 
						{
							return DbType.Decimal;
						}
						else if (precision > 7) 
						{
							return DbType.Double;
						}
						else 
						{
							return DbType.Single;
						}
					}
					else 
					{
						if( precision == 1 ) 
						{ // Highly probably it's a bool, but not always correct :-O
							return DbType.Boolean;
						}
							/* Don't use short datatype, it's pretty useless.
							else if( precision < 5 ) {
								return DbType.Int16;
							} */
						else if( precision < 10 ) 
						{
							return DbType.Int32;
						} 
						else if( precision < 19 ) 
						{
							return DbType.Int64;
						} 
						else 
						{
							return DbType.Decimal;
						}
					}

				case "DATE":
					return DbType.DateTime;

				case "VARCHAR2":
				case "NVARCHAR2":
				case "VARCHAR":
					return DbType.String;

				case "CHAR":
					return DbType.StringFixedLength;

				case "RAW":
					return DbType.Binary;

				case "LONG":
				case "LONG RAW":
				case "CLOB":
				case "NCLOB":
				case "BLOB":
					return DbType.Object;

				default:
					return DbType.Object;
			}
		}

		private System.Data.DbType GetDbType(OracleDataReader reader)
		{
			string nativeType = null;
			nativeType = reader["DATA_TYPE"].ToString();
			
			int scale = 0;
			int precision = 0;

			try
			{
				scale = Convert.ToInt32(reader["DATA_SCALE"]);
			}
			catch {}

			try
			{
				precision = Convert.ToInt32(reader["DATA_PRECISION"]);
			}
			catch {}



			return GetDbType(nativeType, precision, scale);
		}
		#endregion

		private int GetDBTypeLength(string dataTypeName, int length)		
		{
			switch (dataTypeName)
			{
				case "VARCHAR2":
				case "NVARCHAR2":
				case "VARCHAR":
				case "CHAR":
				case "LONG":
				case "RAW":
				case "LONG RAW":
				case "CLOB":
				case "NCLOB":
				case "BLOB":
					return length;

				default:
					// Size is not relevant to other data types
					return 0;
			}
		}

		private int GetDBTypeLength(OracleDataReader reader)
		{
			string nativeType = (string) reader["DATA_TYPE"];
			
			Int32 length = 0;

			try
			{
				length = Convert.ToInt32(reader["DATA_LENGTH"]);
			}
			catch (Exception) {}


			return GetDBTypeLength( nativeType,  length );
		}

		private  System.Data.ParameterDirection GetParameterDirection(bool isReturnVal, string parmDirectionText)
		{
			System.Data.ParameterDirection parmDirection = System.Data.ParameterDirection.ReturnValue;
 
			if (!isReturnVal)
			{
				switch (parmDirectionText)
				{
					case "IN":
						parmDirection = System.Data.ParameterDirection.Input;  
						break;

					case "OUT":
						parmDirection = System.Data.ParameterDirection.Output;  
						break;

					case "IN/OUT":
						parmDirection = System.Data.ParameterDirection.InputOutput;  
						break;

				}
			}

			return parmDirection;
		}

		#region GetConstraintType (commented out)
//		private char GetConstraintType(OracleConnection conn, string ownerName, string tableName, string indexName)
//		{
//			string rVal = null;
//
//			string sql = string.Format( 
//				@"select constraint_type 
//					from all_constraints con
//				   where con.owner = '{0}'
//					 and con.table_name  = '{1}'
//					 and con.constraint_name = '{2}'",
//				ownerName ,
//				tableName, 
//				indexName );
//
//			OracleCommand cmd = new OracleCommand(sql, conn);
//			using (cmd)
//			{
//				OracleDataReader reader = cmd.ExecuteReader();
//
//				while (reader.Read())
//				{
//					rVal = reader.GetString(0);
//				}
//			}
//
//			if ( rVal == null)
//			{
//				return '\0';
//			}
//
//			return rVal[0];
//		}
//
		#endregion

		#endregion

		#region Other junk (commented out)

//		System.Text.StringBuilder sbRet = new StringBuilder();
//		string value;
//		value = value.ToLower() + " ";
//		int p =0 ;
//		do
//	{
//		p = value.IndexOf('_');
//		if (p>=0)
//	{
//		char ch = value[p+1];
//		value.Remove(p,2);
//		value.Insert(p, ch.ToString().ToUpper());
//	}
//} while ( p>=0);

		//	--select * from all_objects
		//	--where object_name like 'USER%'
		//
		//	-- view text
		//	--select v.text from all_views  v where v.owner = 'TOSCAM' and v.view_name = 'TASKMAN_V_RISK_CODES'
		//
		//
		//	-- All views
		//	--select v.owner, v.view_name, o.created
		//	--from all_views   v,
		//	--     all_objects o
		//	--where v.view_name = o.object_name and o.object_type = 'VIEW' and not v.owner in ('SYS', 'SYSTEM', 'OPS$ORACLE')
		//	--order by v.owner, v.view_name
		//	   
		//	-- Columns for a table or view
		//	--select * from  ALL_TAB_COLUMNS
		//	--where table_name = 'TASKMAN_V_EVENTS'
		//	--and owner='TOSCAM'
		//	--order by column_id
		//
		//	/*select * from all_indexes
		//	where table_name = 'TASKMAN_EVENTS'
		//	and owner='TOSCAM'*/
		//
		//	-- This gets you the indexes for a table...
		//	--select UNIQUENESS, TABLE_TYPE, col.*  
		//	--				from		all_indexes idx,
		//	--                            all_ind_columns col
		//	--				where		idx.table_name = 'TASKMAN_EVENTS'
		//	--                and         idx.owner = 'TOSCAM'
		//	--                            AND col.table_owner = idx.owner
		//	--							AND col.index_name = idx.index_name 
		//
		//	-- PK for a table...
		//	--select * from 
		//	--        all_constraints con
		//	--where	con.table_name  = 'TASKMAN_EVENTS'
		//	--  and         con.owner = 'TOSCAM'
		//	--  and constraint_type = 'P'
		//	    
		//
		//	-- commands...
		//	--select distinct owner, PACKAGE_NAME, OBJECT_NAME from ALL_ARGUMENTS  
		//	--where not owner in ( 'SYS' )             
		//
		//	-- SCHAEMAS
		//	--select distinct owner from ALL_OBJECTS
		//	--where not owner in ( 'SYS', 'OPS$ORACLE', 'SYSTEM' )             
		//
		//
		// -- All tables
		// select * from ALL_OBJECTS
		// where not owner in ( 'SYS', 'OPS$ORACLE', 'SYSTEM' )       
		// and object_type='TABLE'      
		// order by owner, object_name
		//
		//	--select * from all_method_results
		//	--where owner = 'TOSCAM'--in 
		//
		//	       
		//
		//	-- ACTIVE SCHEMA
		//	--select USERNAME from user_users 
	#endregion

        public void SetExtendedProperties(string connectionString, SchemaBase schemaObject)
        {
            throw new NotSupportedException("OracleNativeSchemaProvider does not support setting extended property values.");
        }


        #region Provider specific behaviors
        public override void Initialize(string name, NameValueCollection configValue)
        {

        }

        public override string Name
        {
            get
            {
                return "OracleDataAccessDBSchemaProvider";
            }
        }
        #endregion
    }
}


